package cn.fldong.dao.impl;

import cn.fldong.dao.StudentDAO;
import cn.fldong.model.Student;
import cn.fldong.util.DBUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * StudentDAO接口的实现类，提供学生相关的数据库操作实现
 */
public class StudentDAOImpl implements StudentDAO {
    private static final String GET_STUDENT_BY_ID = "SELECT * FROM students WHERE student_id = ?";
    private static final String GET_STUDENTS_BY_NAME = "SELECT * FROM students WHERE full_name LIKE ? ORDER BY student_id";
    private static final String GET_STUDENTS_BY_CLASS_ID = "SELECT * FROM students WHERE class_id = ? ORDER BY student_id";
    private static final String GET_STUDENTS_BY_GRADE = "SELECT * FROM students WHERE grade = ? ORDER BY student_id";
    private static final String GET_STUDENTS_BY_MAJOR = "SELECT * FROM students WHERE major = ? ORDER BY student_id";
    private static final String GET_ALL_STUDENTS = "SELECT * FROM students ORDER BY student_id";
    private static final String ADD_STUDENT = "INSERT INTO students(student_id, full_name, gender, age, grade, major, class_id, birth_date, phone, email, address, remark) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    private static final String UPDATE_STUDENT = "UPDATE students SET full_name=?, gender=?, age=?, grade=?, major=?, class_id=?, birth_date=?, phone=?, email=?, address=?, remark=? WHERE student_id=?";
    private static final String DELETE_STUDENT = "DELETE FROM students WHERE student_id = ?";

    @Override
    public Student getStudentById(String studentId) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        Student student = null;

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(GET_STUDENT_BY_ID);
            stmt.setString(1, studentId);
            rs = stmt.executeQuery();

            if (rs.next()) {
                student = mapResultSetToStudent(rs);
            }
        } finally {
            DBUtil.closeResources(conn, stmt, rs);
        }

        return student;
    }
    
    /**
     * 将ResultSet映射为Student对象
     */
    private Student mapResultSetToStudent(ResultSet rs) throws SQLException {
        Student student = new Student();
        student.setStudentId(rs.getString("student_id"));
        student.setFullName(rs.getString("full_name"));
        student.setGender(rs.getString("gender"));
        student.setAge(rs.getInt("age"));
        student.setGrade(rs.getString("grade"));
        student.setMajor(rs.getString("major"));
        student.setClassId(rs.getString("class_id"));
        student.setBirthDate(rs.getDate("birth_date"));
        student.setPhone(rs.getString("phone"));
        student.setEmail(rs.getString("email"));
        student.setAddress(rs.getString("address"));
        student.setRemark(rs.getString("remark"));
        student.setCreatedAt(rs.getTimestamp("created_at"));
        student.setUpdatedAt(rs.getTimestamp("updated_at"));
        return student;
    }
    
    @Override
    public boolean studentIdExists(String studentId) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT COUNT(*) FROM students WHERE student_id = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, studentId);
            rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
            return false;
        } finally {
            DBUtil.closeResources(conn, stmt, rs);
        }
    }

    @Override
    public List<Student> getStudentsByName(String name) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<Student> studentList = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(GET_STUDENTS_BY_NAME);
            stmt.setString(1, "%" + name + "%"); // 模糊查询
            rs = stmt.executeQuery();

            while (rs.next()) {
                studentList.add(mapResultSetToStudent(rs));
            }
        } finally {
            DBUtil.closeResources(conn, stmt, rs);
        }

        return studentList;
    }

    @Override
    public List<Student> getStudentsByClassId(String classId) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<Student> studentList = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(GET_STUDENTS_BY_CLASS_ID);
            stmt.setString(1, classId);
            rs = stmt.executeQuery();

            while (rs.next()) {
                studentList.add(mapResultSetToStudent(rs));
            }
        } finally {
            DBUtil.closeResources(conn, stmt, rs);
        }

        return studentList;
    }

    @Override
    public List<Student> getStudentsByGrade(String grade) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<Student> studentList = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(GET_STUDENTS_BY_GRADE);
            stmt.setString(1, grade);
            rs = stmt.executeQuery();

            while (rs.next()) {
                studentList.add(mapResultSetToStudent(rs));
            }
        } finally {
            DBUtil.closeResources(conn, stmt, rs);
        }

        return studentList;
    }

    @Override
    public List<Student> getStudentsByMajor(String major) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<Student> studentList = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(GET_STUDENTS_BY_MAJOR);
            stmt.setString(1, major);
            rs = stmt.executeQuery();

            while (rs.next()) {
                studentList.add(mapResultSetToStudent(rs));
            }
        } finally {
            DBUtil.closeResources(conn, stmt, rs);
        }

        return studentList;
    }

    @Override
    public List<Student> getAllStudents() throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<Student> studentList = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(GET_ALL_STUDENTS);
            rs = stmt.executeQuery();

            while (rs.next()) {
                studentList.add(mapResultSetToStudent(rs));
            }
        } finally {
            DBUtil.closeResources(conn, stmt, rs);
        }

        return studentList;
    }

    @Override
    public boolean addStudent(Student student) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(ADD_STUDENT);
            stmt.setString(1, student.getStudentId());
            stmt.setString(2, student.getFullName());
            stmt.setString(3, student.getGender());
            stmt.setInt(4, student.getAge());
            stmt.setString(5, student.getGrade());
            stmt.setString(6, student.getMajor());
            stmt.setString(7, student.getClassId());
            stmt.setDate(8, new java.sql.Date(student.getBirthDate().getTime()));
            stmt.setString(9, student.getPhone());
            stmt.setString(10, student.getEmail());
            stmt.setString(11, student.getAddress());
            stmt.setString(12, student.getRemark());

            return stmt.executeUpdate() > 0;
        } finally {
            DBUtil.closeResources(conn, stmt);
        }
    }

    @Override
    public boolean updateStudent(Student student) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(UPDATE_STUDENT);
            stmt.setString(1, student.getFullName());
            stmt.setString(2, student.getGender());
            stmt.setInt(3, student.getAge());
            stmt.setString(4, student.getGrade());
            stmt.setString(5, student.getMajor());
            stmt.setString(6, student.getClassId());
            stmt.setDate(7, new java.sql.Date(student.getBirthDate().getTime()));
            stmt.setString(8, student.getPhone());
            stmt.setString(9, student.getEmail());
            stmt.setString(10, student.getAddress());
            stmt.setString(11, student.getRemark());
            stmt.setString(12, student.getStudentId());

            return stmt.executeUpdate() > 0;
        } finally {
            DBUtil.closeResources(conn, stmt);
        }
    }

    @Override
    public boolean deleteStudent(String studentId) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(DELETE_STUDENT);
            stmt.setString(1, studentId);
            return stmt.executeUpdate() > 0;
        } finally {
            DBUtil.closeResources(conn, stmt);
        }
    }
}